﻿#include "datasum.h"
#include "ui_datasum.h"
#include <QDateTime>
#include <QMessageBox>
#include <QFileDialog>
#include <QSqlQuery>
#include <QTableWidgetItem>
#include <QAxObject>
#include <QDir>

DataSum::DataSum(QDialog *parent) :
    QDialog(parent),
    ui(new Ui::DataSum)
{
    ui->setupUi(this);
    setWindowFlags(windowFlags() | Qt::WindowMaximizeButtonHint | Qt::WindowMinimizeButtonHint);
    TableWidgetInit();
}

DataSum::~DataSum()
{
    delete ui;
}

void DataSum::on_sumdataButton_clicked()
{
    // 清空表格
    ui->ListData->setRowCount(0);

    // 先加载库存操作记录（出入库记录），按操作时间升序排列（时间最早的在前）
    loadStockOperations();

    // 然后加载当前商品库存（显示为"即时库存"），按商品ID排序
    loadCurrentStock();
}

void DataSum::on_SaveButton_clicked()
{
    // 保存文件扩展名为.xls
    QDateTime time;
    QString strTemp;

    time=QDateTime::currentDateTime();//获取系统时间作为文件名
    strTemp=time.toString("yyyy-MM-dd-hhmmss");

    QString strfilename=QFileDialog::getSaveFileName(this,tr("Excel Files"),QString("%1%2.xls").arg(strTemp,"-库存汇总信息"),tr("Excel Files(*.xls)"));

    if(!strfilename.isEmpty())
    {
        QAxObject *Excel=new QAxObject;
        if(Excel->setControl("Excel.Application"))
        {
            Excel->dynamicCall("SetVisible (bool Visible)",false);
            Excel->setProperty("DisplayAlerts",false);

            QAxObject *workbooks=Excel->querySubObject("WorkBooks");
            workbooks->dynamicCall("Add");
            QAxObject *workbook=Excel->querySubObject("ActiveWorkBook");
            QAxObject *worksheet=workbook->querySubObject("Worksheets(int)",1);
            QAxObject *cell;

            // 写入表头
            for(int i=1;i<=ui->ListData->columnCount();i++)
            {
                cell=worksheet->querySubObject("Cells(int,int)",1,i);
                cell->setProperty("RowHeight",38);
                cell->dynamicCall("SetValue(const QString&)",ui->ListData->horizontalHeaderItem(i-1)->data(0).toString());
            }

            // 写入数据
            for(int i=2;i<=ui->ListData->rowCount()+1;i++)
            {
                for(int j=1;j<=ui->ListData->columnCount();j++)
                {
                    QTableWidgetItem *item = ui->ListData->item(i - 2, j - 1);
                    QString text = item ? item->text() + "\t" : "";
                    QAxObject *cell = worksheet->querySubObject("Cells(int,int)", i, j);
                    cell->dynamicCall("SetValue(const QString&)", text);
                }
            }

            workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(strfilename));
            workbook->dynamicCall("Close()");
            Excel->dynamicCall("Quit()");
            delete Excel;
            Excel=NULL;

            QMessageBox::information(this, "提示", "数据导出成功！");
        }
        else
        {
            QMessageBox::warning(this, "警告", "Excel应用程序不可用，请确保已安装Microsoft Excel");
        }
    }
}

void DataSum::TableWidgetInit()
{
    // 调整列数为13列
    ui->ListData->setColumnCount(13);
    ui->ListData->setRowCount(0);  // 初始为0行，动态添加

    // 设置表头
    ui->ListData->setHorizontalHeaderLabels(
        QStringList() << "操作类型" << "商品编号" << "商品名称" << "数量" << "单价"
                      << "总价" << "库存价值" << "供应商" << "负责人" << "操作时间"
                      << "入库时间" << "出库时间" << "备注");

    // 设置列宽
    ui->ListData->setColumnWidth(0, 100);  // 操作类型
    ui->ListData->setColumnWidth(1, 100);  // 商品编号
    ui->ListData->setColumnWidth(2, 150);  // 商品名称
    ui->ListData->setColumnWidth(3, 80);   // 数量
    ui->ListData->setColumnWidth(4, 80);   // 单价
    ui->ListData->setColumnWidth(5, 100);  // 总价
    ui->ListData->setColumnWidth(6, 100);  // 库存价值
    ui->ListData->setColumnWidth(7, 150);  // 供应商
    ui->ListData->setColumnWidth(8, 100);  // 负责人
    ui->ListData->setColumnWidth(9, 150);  // 操作时间
    ui->ListData->setColumnWidth(10, 150); // 入库时间
    ui->ListData->setColumnWidth(11, 150); // 出库时间
    ui->ListData->setColumnWidth(12, 200); // 备注

    // 设置表格属性
    ui->ListData->setSelectionBehavior(QAbstractItemView::SelectRows);
    ui->ListData->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->ListData->setAlternatingRowColors(true);
    ui->ListData->horizontalHeader()->setStretchLastSection(true);
}

// 加载库存操作记录 - 按操作时间升序排列（时间最早的在前）
void DataSum::loadStockOperations()
{
    QSqlQuery query;
    // 修改排序为升序（ASC），时间最早的排在最前面
    query.prepare("SELECT operation_type, stock_id, stock_name, operation_amount, operator, "
                  "operation_time, remarks FROM stock_operations "
                  "ORDER BY operation_time ASC");  // 改为ASC，时间最早的在前

    if (query.exec())
    {
        int row = 0;
        while (query.next())
        {
            QString operationType = query.value(0).toString();
            QString stockId = query.value(1).toString();
            QString stockName = query.value(2).toString();
            int operationAmount = query.value(3).toInt();
            QString operatorName = query.value(4).toString();
            QDateTime operationTime = query.value(5).toDateTime();
            QString remarks = query.value(6).toString();

            // 设置行数
            ui->ListData->setRowCount(row + 1);

            // 根据操作类型设置不同的显示格式
            QTableWidgetItem *typeItem = new QTableWidgetItem(operationType);
            if (operationType == "入库") {
                typeItem->setBackground(Qt::green);
            } else if (operationType == "出库") {
                typeItem->setBackground(Qt::yellow);
            } else if (operationType == "删除") {
                typeItem->setBackground(Qt::red);
                typeItem->setForeground(Qt::white);
            }

            // 数量显示，入库显示正数，出库显示负数
            QString amountStr = (operationType == "入库") ?
                                    QString("+%1").arg(operationAmount) :
                                    QString("-%1").arg(operationAmount);

            // 计算操作金额
            double operationValue = 0;
            if (operationType == "入库" || operationType == "出库") {
                // 查询商品单价来计算操作金额
                QSqlQuery priceQuery;
                priceQuery.prepare("SELECT StockUnitprice FROM commoditydatatable WHERE StockId = ?");
                priceQuery.addBindValue(stockId);
                if (priceQuery.exec() && priceQuery.next()) {
                    double unitPrice = priceQuery.value(0).toDouble();
                    operationValue = operationAmount * unitPrice;
                    if (operationType == "出库") {
                        operationValue = -operationValue; // 出库显示为负数
                    }
                }
            }

            ui->ListData->setItem(row, 0, typeItem);  // 操作类型
            ui->ListData->setItem(row, 1, new QTableWidgetItem(stockId));
            ui->ListData->setItem(row, 2, new QTableWidgetItem(stockName));
            ui->ListData->setItem(row, 3, new QTableWidgetItem(amountStr));
            ui->ListData->setItem(row, 4, new QTableWidgetItem(""));  // 单价（操作记录不显示）
            ui->ListData->setItem(row, 5, new QTableWidgetItem(QString::number(operationValue, 'f', 2)));  // 操作金额
            ui->ListData->setItem(row, 6, new QTableWidgetItem(""));  // 库存价值（操作记录不显示）
            ui->ListData->setItem(row, 7, new QTableWidgetItem(""));  // 供应商（操作记录不显示）
            ui->ListData->setItem(row, 8, new QTableWidgetItem(operatorName));  // 负责人列显示操作人
            ui->ListData->setItem(row, 9, new QTableWidgetItem(operationTime.toString("yyyy-MM-dd hh:mm:ss")));  // 操作时间
            ui->ListData->setItem(row, 10, new QTableWidgetItem(""));  // 入库时间（操作记录不显示）
            ui->ListData->setItem(row, 11, new QTableWidgetItem(""));  // 出库时间（操作记录不显示）
            ui->ListData->setItem(row, 12, new QTableWidgetItem(remarks));

            row++;
        }
    }
    else
    {
        QMessageBox::warning(this, "警告",
                             QString("查询库存操作记录失败！错误信息：%1").arg(query.lastError().text()));
    }
}

// 加载当前商品库存 - 按商品ID排序
void DataSum::loadCurrentStock()
{
    QSqlQuery query;
    // 修改排序为按商品ID排序
    query.exec("SELECT * FROM commoditydatatable ORDER BY StockId ASC");

    int currentRow = ui->ListData->rowCount();
    double totalInventoryValue = 0;  // 总库存价值
    int totalItems = 0;             // 总商品种类数
    int totalStockAmount = 0;        // 总库存数量

    while (query.next())
    {
        QString stockId = query.value(0).toString();
        QString stockName = query.value(1).toString();
        int stockAmount = query.value(2).toInt();
        double stockUnitPrice = query.value(3).toDouble();
        QString supplier = query.value(4).toString();
        QString director = query.value(5).toString();
        QString warehousingTime = query.value(6).toString();
        QString deliveryTime = query.value(7).toString();
        QString remarks = query.value(8).toString();

        // 计算总价和库存价值
        double totalPrice = stockAmount * stockUnitPrice;

        // 累加统计信息
        totalInventoryValue += totalPrice;
        totalItems++;
        totalStockAmount += stockAmount;

        // 设置行数
        ui->ListData->setRowCount(currentRow + 1);

        // 添加当前库存行
        QTableWidgetItem *typeItem = new QTableWidgetItem("即时库存");
        typeItem->setBackground(Qt::cyan);

        ui->ListData->setItem(currentRow, 0, typeItem);
        ui->ListData->setItem(currentRow, 1, new QTableWidgetItem(stockId));
        ui->ListData->setItem(currentRow, 2, new QTableWidgetItem(stockName));
        ui->ListData->setItem(currentRow, 3, new QTableWidgetItem(QString::number(stockAmount)));
        ui->ListData->setItem(currentRow, 4, new QTableWidgetItem(QString::number(stockUnitPrice, 'f', 2)));
        ui->ListData->setItem(currentRow, 5, new QTableWidgetItem(""));  // 总价列（操作记录用）
        ui->ListData->setItem(currentRow, 6, new QTableWidgetItem(QString::number(totalPrice, 'f', 2)));  // 库存价值
        ui->ListData->setItem(currentRow, 7, new QTableWidgetItem(supplier));
        ui->ListData->setItem(currentRow, 8, new QTableWidgetItem(director));
        ui->ListData->setItem(currentRow, 9, new QTableWidgetItem(""));  // 操作时间留空
        ui->ListData->setItem(currentRow, 10, new QTableWidgetItem(warehousingTime));
        ui->ListData->setItem(currentRow, 11, new QTableWidgetItem(deliveryTime));
        ui->ListData->setItem(currentRow, 12, new QTableWidgetItem(remarks));

        currentRow++;
    }

    // 添加汇总行
    if (totalItems > 0) {
        ui->ListData->setRowCount(currentRow + 1);

        QTableWidgetItem *summaryItem = new QTableWidgetItem("库存汇总");
        summaryItem->setBackground(Qt::lightGray);
        ui->ListData->setItem(currentRow, 0, summaryItem);

        ui->ListData->setItem(currentRow, 1, new QTableWidgetItem(QString("共%1种商品").arg(totalItems)));
        ui->ListData->setItem(currentRow, 2, new QTableWidgetItem("汇总统计"));
        ui->ListData->setItem(currentRow, 3, new QTableWidgetItem(QString::number(totalStockAmount)));
        ui->ListData->setItem(currentRow, 6, new QTableWidgetItem(QString::number(totalInventoryValue, 'f', 2)));
        ui->ListData->setItem(currentRow, 12, new QTableWidgetItem(QString("总库存价值：¥%1").arg(totalInventoryValue, 0, 'f', 2)));

        // 合并单元格显示汇总信息
        ui->ListData->setSpan(currentRow, 1, 1, 2); // 合并第1-2列
    }
}
